package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.CarInfo;
import entity.UserInfo;
import util.DBAccess;

public class CarDao {

	public List<CarInfo> list() {
		return list(0, Short.MAX_VALUE);
	}

	public List<CarInfo> list(int start, int count) {
		List<CarInfo> cars = new ArrayList<CarInfo>();
		String sql = "select * from carinfo order by carid desc limit ?,?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {

			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				CarInfo car = new CarInfo();
				int carId = rs.getInt(1);
				String username = rs.getString(2);
				String carName = rs.getString(3);
				String carType = rs.getString(4);
				String carColor = rs.getString(5);
				String carCard = rs.getString(6);

				car.setCarId(carId);
				car.setUserName(username);
				car.setCarName(carName);
				car.setCarType(carType);
				car.setCarColor(carColor);
				car.setCarCard(carCard);

				cars.add(car);
			}
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cars;
	}

	public void add(CarInfo car) {
		String sql = "insert into carinfo values(null,?,?,?,?,?)";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, car.getUserName());
			ps.setString(2, car.getCarName());
			ps.setString(3, car.getCarType());
			ps.setString(4, car.getCarColor());
			ps.setString(5, car.getCarCard());

			ps.execute();

			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(int id) {
		String sql = "delete from carinfo where carid = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, id);
			ps.execute();

			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void update(CarInfo car) {
		String sql = "update carinfo set userName = ?, carname = ?, cartype = ?, carcolor = ?, carcard = ? where carid = ? ";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{
			ps.setString(1, car.getUserName());
			ps.setString(2, car.getCarName());
			ps.setString(3, car.getCarType());
			ps.setString(4, car.getCarColor());
			ps.setString(5, car.getCarCard());
			ps.setInt(6, car.getCarId());

			ps.execute();

			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int getTotal() {
		int total = 0;
		String sql = "select count(*) from carinfo";
		try(Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	//根据用户名或汽车编号获取汽车信息
	public List<CarInfo> query(String value){
		List<CarInfo> cars = new ArrayList();
		
		String sql = "select * from carinfo where username = ? or carid= ? ";
		try(Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{	
			ps.setString(1, value);
			ps.setString(2, value);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				CarInfo car = new CarInfo();
				car.setCarId(rs.getInt(1));
				car.setUserName(rs.getString(2));
				car.setCarName(rs.getString(3));
				car.setCarType(rs.getString(4));
				car.setCarColor(rs.getString(5));
				car.setCarCard(rs.getString(6));
				cars.add(car);
			}
			ps.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cars;
	}

	
	
	//通过汽车编号获取汽车信息
	public CarInfo get(int carid) {
		String sql = "select * from carinfo where carid = ?";
		CarInfo car = new CarInfo();
		try(Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) 
		{
			ps.setInt(1, carid);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				car.setCarId(carid);
				car.setUserName(rs.getString(2));
				car.setCarName(rs.getString(3));
				car.setCarType(rs.getString(4));
				car.setCarColor(rs.getString(5));
				car.setCarCard(rs.getString(6));
			}
			ps.close();
			conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return car;
	}

	
}
